Containerized FOSS Analytical DBMS solutions w duckdb and minio

Markus Skyttner / markus@redpill-linpro.com

2022-10-14

Om mig

Uppdrag på KTH: Ursprungligen “Årlig Bibliometrisk Uppföljning på KTH” / “Annual Bibliometric Monitoring”

Nu: Datadriven analys och uppföljning av KTHs forskning

Om FOSS analytics med duckdb och minio

  • Vad är “Analytical DBMS” och “object storage”?
  • Vad är duckdb och minio och varför dessa komponenter?
  • Demo med lite exempeldata
  • Workshop - prova på?

Varför använda “Analytical DBMS”?

Vill kunna jobba med snabba frågor mot stora dataset för att göra analyser snabbt. Här finns många FOSS-lösningar, både molnbaserade kommersiella och hybridlösningar.

  • Ett populärt FOSS-verktyg för att jobba med större dataset är Apache Spark där bla DataBricks erbjuder en tjänstelösning.
  • Ett annat är bra verktyg är ClickHouse som också har en molnlösning.
  • Det finns också Postgresbaserade alternativ med tjänstelösningar i molnet som GreenPlum.

Data samlas gärna så att den är åtkomlig via S3-protokollet.

  • Olika molnlösningar finns från Amazon, GCP, Azure, DO, Backblaze etc.

Ofta ligger fokus på omfattande snabba läsningar snarare än många samtidiga skrivningar till datakällan.

Vad är minio?

En populär FOSS-komponent för S3-lagring. Mer eller mindre standard för “S3”-kompatibel lagring m server, CLI-verktyg mm.

Kan man köra databaser mot “object storage”? Blir det inte långsamt?

Vad är duckdb?

Varför är duckdb intressant?

Ju långsammare nätverk desto längre till data…

“Bring the engine to the data”

… vi använder därför containers på samma SDN (Software Defined Network) för låg nätverkslatens mellan databasmotor och data så blir det praktiskt och snabbt, och kan köras på hemmaplan och/eller i de dimmiga molnen.

Kombinationen duckdb och minio

Behöver man kolumnbaserat/OLAP eller radorienterad/OLTP, eller både och?

En kombination av minio och duckdb ger en minimal men kraftfull hybrid-moln-kapabel lösning baserad på FOSS-containers som erbjuder många integrationsmöjligheter även mot OLTP-källor, S3/parquet mm.

Lite som en “bare bones” Apache Spark-miljö. Där man kan jobba med data från olika miljöer såsom R, Python, Java, Go, Node etc eller exv använda verktyg som CloudBeaver för DBAs mm (se oneliner nedan).

mkdir -p /tmp/workspace && \
  podman  run --name cloudbeaver --rm -ti \
    -p 8080:8978 -v /tmp/workspace:/opt/cloudbeaver/workspace \
    dbeaver/cloudbeaver:latest

Ladda exempeldata i “duckdb”

Vi använder lite exempeldata från digitala vetenskapliga arkivet.

library(kthcorpus) # from https://github.com/kth-library/kthcorpus

# get some example publication data from DiVA (digitala vetenskapliga arkivet)
publications <- kth_diva_pubs()
authors <- kth_diva_authors()

Mjukvaran från duckdb är en kolumnorienterad inbäddningsbar “in-process” OLAP-motor. Här kopplar vi direkt till ovanstående data (in-memory) och kör en klassisk SQL-fråga:

library(dplyr)
library(duckdb)

# load it into a RDBMS (duckdb)
con <- dbConnect(duckdb())
duckdb_register(con, "publications", publications)
duckdb_register(con, "authors", authors)

# now we can use custom SQL queries
con %>% dbGetQuery("SELECT count(*) from authors a left join publications p on a.PID = p.PID")
  count_star()
1       306912

Mer än SQL? Tidyverse

Tidyverse erbjuder kraftfulla API:er som gör allt SQL kan och mer därtill på ett ergonomiskt sätt.

# tidyverse APIs used for data manipulation with "chaining" (using pipes)
top_three <- 
  con %>% 
  tbl("publications") %>% 
  group_by(PublicationType) %>%
  tally() %>%
  head(3)

print(top_three)
# Source:   lazy query [?? x 2]
# Database: duckdb_connection
  PublicationType                 n
  <chr>                       <dbl>
1 Artikel i tidskrift         35691
2 Konferensbidrag             17007
3 Artikel, forskningsöversikt   722

Quarto för webvänlig presentation

Visning av denna data med quarto och klassisk pivottabell i interaktivt HTML-format:

# data summary
mysummary <- 
  publications %>% 
  group_by(
    PublicationType, Language, 
    Funder, Categories, Reviewed) %>% 
  tally() 

# display as a pivot table
rpivotTable::rpivotTable(
  mysummary, 
  width = "100%", height = "500px",
  rows = "PublicationType",
  cols = c("Language")
)

Quarto för webvänlig presentation

Dataformat - Arrow, Parquet mm

Vi kan använda moderna format såsom Arrow och Parquet med duckdb:

library(arrow)

# write parquet data from in-memory data
write_parquet(publications, "/tmp/publications.parquet")
write_parquet(authors, "/tmp/authors.parquet")

# read this data from parquet format using duckdb
con <- dbConnect(duckdb())

mypubs <- con %>% 
  dbGetQuery("SELECT * FROM PARQUET_SCAN('/tmp/publications.parquet')")

# query using tidyverse APIs
mypubs |> 
  filter(grepl("Java", Abstract)) |>
  arrange(desc(PublicationDate)) |>
  slice(1:50) |> 
  select(Title, PublicationDate, Abstract) |> 
  mutate(Abstract = gsub("</?p>", "", Abstract)) |> 
  DT::datatable()

Dataformat - Arrow, Parquet mm

dbDisconnect(con)

Container setup för minio och duckdb

  • För minio - använd antingen officiella imagen från Docker Hub, eller överväg en “trimmad”, se länk: https://github.com/KTH-Library/docker-alpine-minio-client - som då behöver hållas up-to-date exv m en GitHub Action med schemalagt bygge.

  • Varför lägga till en container image med duckdb? Eftersom då flyttas minio server och duckdb tillsammans inom samma nätverk och låg latens uppnås mellan dessa.

Exempel på docker-compose.yml

Exempel på en sådan setup av minio och duckdb mm finns i repot här https://github.com/mskyttner/duckdbrest:

Vi kan exv köra dessa tjänster tillsammans (duckdb m webinterface via gotty, minio, postgres och nginx):

version: '3.7'

services:

  gotty:
    image: duckdbrest:v0.5.1
    ports:
      - "8080:1294"
    command: sh -c "envsubst < /data/init.sql > ~/.duckdbrc && gotty --port ${PORT:-1294} --permit-write --reconnect duckdb -unsigned -interactive myduck.db -readonly"
    environment:
      - MC_HOST_s3=http://minioadmin:minioadmin@minio:9000
      - S3_ENDPOINT='minio:9000'
      - S3_USER='minioadmin'
      - S3_PASS='minioadmin'
      - S3_USE_SSL=false
      - S3_REGION=''
      - S3_URL_STYLE='path'
      - S3_BUCKET=demo
    volumes:
      - ./init.sql:/data/init.sql

  bashapi:
    image: duckdbrest:v0.5.1
    ports:
      - "8181:8181"
    command: bash -c "cd /bash-cli-api && DDB_PATH=/data/myduck.db ./index.sh"

  sherver:
    image: duckdbrest:v0.5.1
    ports:
      - "8282:8282"
    command: bash -c "cd /sherver && ./sherver.sh 8282 2> /proc/1/fd/1"

  minio:
    image: quay.io/minio/minio
    command: server /data --console-address ":9001"
    environment:
      - MINIO_ROOT_USER=minioadmin
      - MINIO_ROOT_PASSWORD=minioadmin
      - MINIO_API_SELECT_PARQUET=on
      - MINIO_BROWSER_REDIRECT_URL=http://localhost/minio
    ports:
      - 9000:9000
      - 9001:9001
    volumes:
      - ./s3:/data

  nginx:
    image: nginx:alpine
    volumes:
      - ./mitm.conf:/etc/nginx/conf.d/default.conf:ro
    ports:
      - 80:80

  postgres:
    image: postgres:14.5-alpine
    environment:
      POSTGRES_DB: 'post'
      POSTGRES_USER: 'post'
      POSTGRES_PASSWORD: 'post'
    ports:
      - 5432:5432

Frågor från duckdb mot data i S3, postgres etc

Om vi laddar data till Postgres…. exv dessa tidigare två dataset…

library(DBI)

con <- dbConnect(
  drv = RPostgres::Postgres(), 
  host = "127.0.0.1", port = 5432, 
  user = "post", password = "post")

con %>% dbListTables()

con %>% dbWriteTable("authors", authors)
con %>% dbWriteTable("publications", publications)

con %>% dbListTables()

dbDisconnect(con)

Join av data i postgres och S3/parquet

… då kan vi sedan använda duckdb för att ställa en fråga där vanliga duckdb-tabeller, fjärran S3-data och data i Postgres och SQLite kan kombineras effektivt utan flera steg av mellanlagring.

Förenklat exempel på fråga att använda i “websocket gotty-klienten”:

select Title from postgres_scan('dbname=post host=postgres user=post password=post', 'public', 'authors') a left join (select * from 's3://demo/publications.parquet') p on a.PID = p.PID limit 5;

Framtiden med WASM, quarto mm

  • Det kommer ett duckdb-paket på pyodide, “work in progress”

  • Kommer då kunna användas i quarto-dokument med extensions för “live documents” med inbäddade appar, se detta exempel med källkod här - en app som då kan deployas på “statisk sajt”.

  • Denna presentation är ett “quarto”-dokument (skrivs i markdown med inbäddad kod, typ som en “Python notebook” men använder inte ett binärt format).

Prova på?

Komponenter finns här:

Repositories med material finns här: